﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Odbc;
using ATM.Entities;

namespace ATM.DataAccess
{
    /// <summary>
    /// Thao tac du lieu voi cac thuc the Account
    /// </summary>
    public class AccountDA
    {
        /// <summary>
        /// Lay ve thong tin tai khoan
        /// </summary>
        /// <param name="cardId">Ma the ATM</param>
        /// <returns></returns>
        public Account GetAccount(string cardId)
        {
            Account acc = new Account();
            string cmd = string.Format("SELECT Account.AccountNumber, Account.Balance FROM Account INNER JOIN DebitCard ON Account.AccountNumber = DebitCard.AccountNumber WHERE (((DebitCard.CardNumber)=\"{0}\"));", cardId);
            DataRow dr = SqlHelper.ExcuteQuery(cmd).Rows[0];
            acc.AccountId = dr[0].ToString();
            acc.Balance = Convert.ToDouble(dr[1].ToString());
            return acc;
        }
       /// <summary>
       /// Lay so du tai khoan
       /// </summary>
       /// <param name="cardId">Ma the</param>
       /// <returns>So du tai khoan</returns>
        public double GetBalance(string cardId)
        {
            double balance;
            string query = string.Format("SELECT Sum(Transaction.Amount) AS SumOfAmount FROM DebitCard INNER JOIN [Transaction] ON DebitCard.CardNumber = Transaction.CardNumber WHERE (((Transaction.TransTime)=Date()) AND ((DebitCard.CardNumber)=\"{0}\")) GROUP BY DebitCard.CardNumber;", cardId);
            DataTable dt = new DataTable();
            dt = SqlHelper.ExcuteQuery(query);
            if (dt.Rows.Count == 0)
            {
                return 0;
            }
            else
            {
                balance = Convert.ToDouble(dt.Rows[0][0]);
            }
            return balance;
        }

        /// <summary>
        /// Them mot the moi
        /// </summary>
        /// <param name="card">Doi tuong Card</param>
        /// <returns></returns>
        public int AddNewCard(Card card)
        {
            string query = string.Format("INSERT INTO DebitCard ( CardNumber, CardPIN, AccountNumber, OpenedDate, EndDate, Status ) SELECT \"{0}\" AS Expr1, \"{1}\" AS Expr2, \"{2}\" AS Expr3, \"{3}\" AS Expr4, \"{4}\" AS Expr5, {5} AS Expr6;",
                card.CardId, card.CardPin, card.AccountId,card.CreateDate, card.EndDate, card.Status);
            int result = SqlHelper.ExcuteNonQuery(query);
            return result;

        }

        /// <summary>
        /// Thay doi thong tin the
        /// </summary>
        /// <param name="cardId">Ma the</param>
        /// <param name="cardStatus">trang thai</param>
        /// <returns></returns>
        public int ModifyCardValidity(string cardId, int cardStatus)
        {
            string query = string.Format("UPDATE DebitCard SET DebitCard.Status = {0} WHERE (((DebitCard.CardNumber)=\"{1}\"));", cardStatus, cardId);
            int result = SqlHelper.ExcuteNonQuery(query);
            return result;
        }

        /// <summary>
        /// Kiem tra Account nay co bi lock hay khong
        /// </summary>
        /// <param name="accountId">Ma tai khoan</param>
        /// <returns>True/False</returns>
        public bool CheckAccountIsLocked(string accountId)
        {
            bool check = false;
            string query = string.Format("SELECT Account.Status FROM Account WHERE (((Account.AccountNumber)=\"{0}\"))", accountId);
            DataTable dt = new DataTable();
            dt = SqlHelper.ExcuteQuery(query);
            int stt = Convert.ToInt32(dt.Rows[0][0].ToString());
            if (stt == 1)
                check = false;
            else
                check = true;
            return check;
        }

        /// <summary>
        /// Lay ve gia tri OverDraft cua moi the
        /// </summary>
        /// <param name="cardId">Ma the</param>
        /// <returns>Overdraft limit</returns>
        public double OverDraft(string cardId)
        {
            double overDraft = 0;
            string query = string.Format("SELECT Account.OD FROM Account INNER JOIN DebitCard ON Account.AccountNumber = DebitCard.AccountNumber WHERE  ((DebitCard.CardNumber)=\"{0}\");", cardId);
            DataTable dt = new DataTable();
            dt = SqlHelper.ExcuteQuery(query);
            overDraft = Convert.ToDouble(dt.Rows[0][0].ToString());
            return overDraft;
        }

        /// <summary>
        /// Kiem tra account co ton tai tren he thong
        /// </summary>
        /// <param name="accountId">Ma Account</param>
        /// <returns>1/0</returns>
        public int CheckAccountIsExist(string accountId)
        {
            string query = string.Format("SELECT 1 FROM Account WHERE Account.AccountNumber = \"{0}\"", accountId);
            DataTable dt = new DataTable();
            dt = SqlHelper.ExcuteQuery(query);
            int result = dt.Rows.Count;
            return result;

        }

        /// <summary>
        /// Kiem tra the co ton tai
        /// </summary>
        /// <param name="cardId">Ma the</param>
        /// <returns></returns>
        public int CheckCardId(string cardId)
        {
            string query = string.Format("SELECT 1 FROM DebitCard WHERE DebitCard.CardNumber = \"{0}\"", cardId);
            DataTable dt = new DataTable();
            dt = SqlHelper.ExcuteQuery(query);
            int result = dt.Rows.Count;
            return result;
        }
    }
}
